package load;

import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Properties;

import au.com.bytecode.opencsv.CSVReader;

/**
 * Bulk loads all of the data contained within CSV files into the database.
 * 
 * @author Brad Hanlon
 *
 */
public class LoadData {

	private Connection conn;
	private Properties csv_files;
	
	/** 
	 * @param conn - Database connection -> get from DatabaseConnector
	 * @param csv_files - Properties file contain csv file information
	 */
	public LoadData(Connection conn, Properties csv_files){
		this.conn = conn;
		this.csv_files = csv_files;
	}
	
	/**
	 * Loads all of the data into the database.
	 */
	public void LoadAll(){
		loadMemberData();
		loadNonMemberData();
		loadVendorData();
		loadTransactionData();
	}
	
	/**
	 * Loads all of the transaction data into the database.
	 */
	private void loadTransactionData(){
		try{
			CSVReader reader = new CSVReader(new FileReader(csv_files.getProperty("transaction")));
		    String [] nextLine;
		    int i = 1;
		    while ((nextLine = reader.readNext()) != null) {
		    	// Get the information from the line in the csv file.
		        String date = nextLine[0];
		        String cat_Subcat = nextLine[1];
		        String action = nextLine[2];
		        String checkNo = nextLine[3];
		        String associate = nextLine[4];
		        String payment = getValue(nextLine[5]);
		        String deposit = getValue(nextLine[6]);
		        String note = formatPhoneNumber(nextLine[7]);
		        
		        // Insert into associate first
				PreparedStatement ps;
				String query = "INSERT INTO Transactions(transDate,category,subCategory,paymentAmount,checkNumber,action,associateId,note,depositAmount) values(?,?,?,?,?,?,?,?,?)";
				ps = conn.prepareStatement(query);
				
				// Date
				java.sql.Date d = getDate(date);
				ps.setDate(1, d);
				// cat
				String category = getCategory(cat_Subcat);
				ps.setString(2, category);
				// subCat
				String subcategory = getSubcategory(cat_Subcat);
				ps.setString(3, subcategory);
				// payment
				if(payment.length() > 0)
					ps.setDouble(4, Double.parseDouble(payment));
				else
					ps.setDouble(4, 0);
				// check
				if(checkNo.length() > 0)
					ps.setInt(5, Integer.parseInt(checkNo));
				else
					ps.setInt(5, -1);
				// action
				ps.setString(6, action);
				// associate
				if(associate.length() >= 32)
					ps.setString(7, associate.substring(0, 31));
				else
					ps.setString(7, associate);
				// note
				ps.setString(8, note);
				// deposit
				if(deposit.length() > 0)
					ps.setDouble(9, Double.parseDouble(deposit));
				else
					ps.setDouble(9, 0);
				ps.execute();
				
				System.out.println("Transaction - " + i + " inserted");
				
				// Insert into TransHasAssociate
		        if(!associate.contains("and")){
					// Insert into TransHasAssociate
					
					// Get the AssociateID from the associate
					int associateID = getAssociateID(associate);
					
					if(associateID != -1){
						query = "INSERT INTO TransHasAssociate(transID,idNum) values(?,?)";
						ps = conn.prepareStatement(query);
						ps.setInt(1,i);
						ps.setInt(2,associateID);
						ps.execute();					
					}
					else{
						System.out.println("NOT FOUND: " + associate);
					}
				}		
				else{
					
				}
				
				i++;
		    }	    
		}
		catch(IOException e){
			e.printStackTrace();
		}
		catch(SQLException e){
			e.printStackTrace();
		}
	}
	
	/**
	 * Returns the appropriate associateID from the Associate table given a name.
	 * 
	 * @param name
	 * @return The associateID if the associate is found, else returns -1
	 */
	private int getAssociateID(String name){
		try{
			String firstName = getFirstName(name);
			String lastName = getLastName(name);
			// Check Member
			PreparedStatement ps;
			String query = "SELECT idNum FROM Member WHERE firstName = ? AND lastName = ?";
			ps = conn.prepareStatement(query);
			ps.setString(1, firstName);
			ps.setString(2, lastName);
			ResultSet rs = ps.executeQuery();
			if(rs.next()){
				return rs.getInt(1);
			}
			// Check Non-member
			query = "SELECT idNum FROM NonMember WHERE fullName = '" + name + "'";
			ps = conn.prepareStatement(query);
			rs = ps.executeQuery();
			if(rs.next()){
				return rs.getInt(1);
			}
			// Check Vendor
			query = "SELECT idNum FROM Vendor WHERE vendorName = '" + name + "'";
			ps = conn.prepareStatement(query);
			rs = ps.executeQuery();
			if(rs.next()){
				return rs.getInt(1);
			}
			return -1;
		}
		catch(SQLException e){
			e.printStackTrace();
		}
		return -1;
	}
	
	/**
	 * Returns the last name in a string formatted in the format: "firstName lastName".
	 * 
	 * @param s
	 * @return
	 */
	private String getLastName(String s){
		if(s.contains(" "))
			return s.substring(s.indexOf(' ')+1,s.length());
		else
			return s;
	}
	
	/**
	 * Returns the first name in a string formatted in the format: "firstName lastName".
	 * 
	 * @param s
	 * @return
	 */
	private String getFirstName(String s){
		if(s.contains(" "))
			return s.substring(0,s.indexOf(' '));
		else
			return s;
	}
	
	/**
	 * Returns a java.sql.Date from a string containing a date.
	 * 
	 * @param s
	 * @return
	 */
	private java.sql.Date getDate(String s){
		try {
			SimpleDateFormat sf = new SimpleDateFormat("MM/dd/yy");
			Date d = sf.parse(s);
			return new java.sql.Date(d.getTime());
		} catch (ParseException e) {
			e.printStackTrace();
		}
		return new java.sql.Date(0);		
	}
	
	/**
	 * Returns the category from a string containing both a category and subcategory.
	 * 
	 * @param s
	 * @return
	 */
	private String getCategory(String s){
		if(s.contains(":")){
			String t = s.substring(0,s.indexOf(':'));
			return t;
		}
		else
			return s;
	}
	
	/**
	 * Returns the subcategory from a string containing both a category and subcategory.
	 * If the string does not have a subcategory, then the empty string is returned.
	 * 
	 * @param s
	 * @return
	 */
	private String getSubcategory(String s){
		if(s.contains(":")){
			String t = s.substring(s.indexOf(':')+1,s.length());
			return t;
		}
		else
			return "";
	}
	
	/**
	 * Returns a string without the '$' sign.
	 * 
	 * @param s
	 * @return
	 */
	private String getValue(String s){
		if(s.length() > 0){
			if(s.contains("-"))
				return "-" + s.substring(s.indexOf('$')+1,s.length());
			else
				return s.substring(s.indexOf('$')+1,s.length());
		}
		else
			return "0";
	}
	
	/**
	 * Loads the member data into the database.
	 */
	private void loadMemberData(){
		try{
			CSVReader reader = new CSVReader(new FileReader(csv_files.getProperty("member")));
		    String [] nextLine;
		    int i = 1;
		    while ((nextLine = reader.readNext()) != null) {
		    	// Get the information from the line in the csv file.
		        String lastName = nextLine[0];
		        String firstName = nextLine[1];
		        String address = nextLine[2];
		        String city = nextLine[3];
		        String state = nextLine[4];
		        String zip = nextLine[5];
		        String zip4 = nextLine[6];
		        String phone = formatPhoneNumber(nextLine[7]);
		        String email = nextLine[8];
		        
		        // Insert into associate first
				PreparedStatement ps;
				String query = "INSERT INTO Associate(address,city,state,zip,zip4,phoneNumber,email) values(?,?,?,?,?,?,?)";
				ps = conn.prepareStatement(query);
				ps.setString(1, address);
				ps.setString(2, city);
				ps.setString(3, state);
				ps.setInt(4, Integer.parseInt(zip));
				ps.setInt(5, Integer.parseInt(zip4));
				ps.setString(6, phone);
				ps.setString(7, email);
				ps.execute();
				
				// Get the idNum for the last inserted associate
				query = "SELECT idNum FROM Associate WHERE address = ? AND city = ? AND state = ? AND zip = ? AND zip4 = "
						+ "? AND phoneNumber = ? AND email = ? AND idNum NOT IN (SELECT idNum FROM Member)";
				ps = conn.prepareStatement(query);
				ps.setString(1, address);
				ps.setString(2, city);
				ps.setString(3, state);
				ps.setInt(4, Integer.parseInt(zip));
				ps.setInt(5, Integer.parseInt(zip4));
				ps.setString(6, phone);
				ps.setString(7, email);
				
				// Get the idNum from the ResultSet
				ResultSet rs = ps.executeQuery();
				rs.next();
				Integer idNum = rs.getInt(1);
				
				// Insert the member associated with the associate idNum
				query = "INSERT INTO Member(idNum, firstName, lastName) values(?,?,?)";
				ps = conn.prepareStatement(query);
				ps.setInt(1, idNum);
				ps.setString(2, firstName);
				ps.setString(3, lastName);
				ps.execute();
				System.out.println("Inserted " + i + " into Associate - Member");
				i++;
		    }
		}
		catch(IOException e){
			e.printStackTrace();
		}
		catch(SQLException e){
			e.printStackTrace();
		}
	}
	
	/**
	 * Load all Non-member data.
	 */
	private void loadNonMemberData(){
		try{
			CSVReader reader = new CSVReader(new FileReader(csv_files.getProperty("non_member")));
		    String [] nextLine;
		    int i = 1;
		    while ((nextLine = reader.readNext()) != null) {
		    	// Get the information from the line in the CSV file
		        String fullName = nextLine[0];
		        String address = nextLine[1];
		        String city = nextLine[2];
		        String state = nextLine[3];
		        String zip = nextLine[4];
		        String zip4 = nextLine[5];
		        String phone = formatPhoneNumber(nextLine[6]);
		        String email = nextLine[7];
		        
		        // Insert into associate
				PreparedStatement ps;
				String query = "INSERT INTO Associate(address,city,state,zip,zip4,phoneNumber,email) values(?,?,?,?,?,?,?)";
				ps = conn.prepareStatement(query);
				ps.setString(1, address);
				ps.setString(2, city);
				ps.setString(3, state);
				ps.setInt(4, Integer.parseInt(zip));
				ps.setInt(5, Integer.parseInt(zip4));
				ps.setString(6, phone);
				ps.setString(7, email);
				ps.execute();
				
				// Get the idNum for the last inserted associate
				query = "SELECT idNum FROM Associate WHERE address = ? AND city = ? AND state = ? AND zip = ? AND zip4 = "
						+ "? AND phoneNumber = ? AND email = ? AND idNum NOT IN (SELECT idNum FROM NonMember)";
				ps = conn.prepareStatement(query);
				ps.setString(1, address);
				ps.setString(2, city);
				ps.setString(3, state);
				ps.setInt(4, Integer.parseInt(zip));
				ps.setInt(5, Integer.parseInt(zip4));
				ps.setString(6, phone);
				ps.setString(7, email);
				
				// Get the idNum from the ResultSet
				ResultSet rs = ps.executeQuery();
				rs.next();
				Integer idNum = rs.getInt(1);
				
				// Insert the non-member associated with the associate idNum
				query = "INSERT INTO NonMember(idNum, fullName) values(?,?)";
				ps = conn.prepareStatement(query);
				ps.setInt(1, idNum);
				ps.setString(2, fullName);
				ps.execute();
				System.out.println("Inserted " + i + " into Associate - NonMember");
				i++;
		    }
		}
		catch(IOException e){
			e.printStackTrace();
		}
		catch(SQLException e){
			e.printStackTrace();
		}
	}
	
	/**
	 * Load all of the vendor data into the database.
	 */
	private void loadVendorData(){
		try{
			CSVReader reader = new CSVReader(new FileReader(csv_files.getProperty("vendor")));
		    String [] nextLine;
		    int i = 1;
		    while ((nextLine = reader.readNext()) != null) {
		    	// Get the information from the line in the csv file.
		        String vendorName = nextLine[0];
		        String address = nextLine[1];
		        String phone = formatPhoneNumber(nextLine[2]);
		        String email = nextLine[3];
		        String city = nextLine[4];
		        String state = nextLine[5];
		        String zip = nextLine[6];
		        
		        // Insert into associate
				PreparedStatement ps;
				String query = "INSERT INTO Associate(address,city,state,zip,phoneNumber,email) values(?,?,?,?,?,?)";
				ps = conn.prepareStatement(query);
				ps.setString(1, address);
				ps.setString(2, city);
				ps.setString(3, state);
				ps.setInt(4, Integer.parseInt(zip));
				ps.setString(5, phone);
				ps.setString(6, email);
				ps.execute();
				
				// Get the idNum for the last inserted associate
				query = "SELECT idNum FROM Associate WHERE address = ? AND city = ? AND state = ? AND zip = ? "
						+ "AND phoneNumber = ? AND email = ? AND idNum NOT IN (SELECT idNum FROM Vendor)";
				ps = conn.prepareStatement(query);
				ps.setString(1, address);
				ps.setString(2, city);
				ps.setString(3, state);
				ps.setInt(4, Integer.parseInt(zip));
				ps.setString(5, phone);
				ps.setString(6, email);
				
				// Get the idNum from the ResultSet
				ResultSet rs = ps.executeQuery();
				rs.next();
				Integer idNum = rs.getInt(1);
				
				// Insert the vendor associated with the associate idNum
				query = "INSERT INTO Vendor(idNum, vendorName) values(?,?)";
				ps = conn.prepareStatement(query);
				ps.setInt(1, idNum);
				ps.setString(2, vendorName);
				ps.execute();
				System.out.println("Inserted " + i + " into Associate - Vendor");
				i++;
		    }
		}
		catch(IOException e){
			e.printStackTrace();
		}
		catch(SQLException e){
			e.printStackTrace();
		}
	}
	
	/**
	 * Takes out all of the parentheses, dashes, and spaces from a phone number.
	 * 
	 * Ex. (555) 666-7777 -> 5556667777
	 * 
	 * @param s - String to format
	 * @return The properly formatted String
	 */
	private String formatPhoneNumber(String s){
		String temp = "";
		for(int i = 0; i < s.length(); i++){
			char c = s.charAt(i);
			if(!(c == '(' || c == ')' || c == '-' || c == ' '))
				temp += c;
		}
		return temp;
	}
	
}
